import java.io.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class SearchItem extends HttpServlet
{
	private String link, query;
	private Connection connection;
	
	public void doGet(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException
	{
		res.setContentType("text/html");
		PrintWriter out = res.getWriter();
		
		out.println("<html><body>" +
				"<form action=\"" +
				"\" method=\"post\">" +
				"<h1><center>SEARCH ITEM</center></h1>" +
				"<h3><center>SF Online Inventory System</center><h3>" +
				"<table align=\"center\" border=\"1\">" +
				"<tr><td><font face=\"Trebuchet MS\" color=\"red\">SEARCH ITEMS</font></td>" +
				"<tr><td>Search only: </td> " +
				"<td><select name=\"query1\">" +
				     "<option selected>Select</option>" +
				     "<option value=\"item_code\">Item Code</option>" +
				     "<option value=\"item_name\">Item Name</option>" +
				     "<option value=\"item_detail\">Item Detail</option>" +
				     "<option value=\"category_code\">Category Code</option></td></tr>" +
				"<tr><td>Value:</td>" +
				"<td><input type=\"text\" name=\"queryValue1\"></td></tr>" +
				"<tr align=\"center\"><td><input type=\"submit\" value=\"Search\"></td>" +
				"<tr><tdView all contents</td>" +
				"</table>" +
				"</form> "+
				
				"<p>" +
				"<form action=\"" +
				"\" method=\"post\">" +
				"<table align=\"center\" border=\1\">" +
				"<tr><td><font face=\"Trebuchet MS\" color=\"red\">SEARCH CATEGORY</font></td>" +
				"<tr><td>Search only: </td>" +
				"<td><select name=\"query2\">" +
				      "<option selected>Select</option>" +
				      "<option value=\"category_code\">Category Code</option>" +
				      "<option value=\"category_name\">Category Name</option>" +
				      "<option value=\"subcategory_code\">Subcategory Code</option></td></tr>" +
				"<tr><td>Value:</td>" +
				"<td><input type=\"text\" name=\"queryValue2\"></td></tr>" +
				"<tr align=\"center\"><td><input type=\"submit\" value=\"Search\"></td>" +
				"</table></form></body></html>");
		out.close();
	}
	
	public void doPost(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException 
    {
		res.setContentType("text/html");
    	PrintWriter out = res.getWriter();
    	
    	out.println("<html><body>");
    	
    	String itemQuery = req.getParameter("query1");
    	String itemQueryValue = req.getParameter("queryValue1");
    	String categoryQuery = req.getParameter("query2");
    	String categoryQueryValue = req.getParameter("queryValue2");
    	
    	try
    	{
    		if (itemQuery != null && itemQueryValue != null)
    			query = ("Select '" +  itemQuery + "'" + "from item where '" + itemQuery + "'" + "= '" + itemQueryValue + "'");
    		if (categoryQuery != null && categoryQueryValue != null)
    			query = ("Select '" + categoryQuery + "'" + "from category where '" + categoryQuery + "'" + "= '" + categoryQueryValue + "'");
    		
    		Class.forName("com.mysql.jdbc.Driver");
    		link = "jdbc:mysql://localhost/inventory";
    		connection = DriverManager.getConnection(link, "root", "");
    		Statement stmt = connection.createStatement();
    		ResultSet result = stmt.executeQuery(query);
    		
			out.println("<html><title>QUERY SUCCESSFUL! RETRIEVING DATA</title>" +
					"<body>");
			out.println();
			out.println("</body></html>");
    	}
    	catch (Exception e)
		{
			out.println("<html><body background color=red>"+e+"</body></html>");
		}
    	
    }
}
